﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DataAccess_Layer
{
    public class DataAccessHelper
    {
        private static DataClasses_RetailerDataContext db = new DataClasses_RetailerDataContext(ConnectionManager.ConnectionString);

        public static DataClasses_RetailerDataContext DB
        {
            get { return db; }
        }
      
        SqlConnection conn = null;

        public DataAccessHelper()
        {
            //solveASPDataDirectory();
            
            //conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SuperStar.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
            conn = new SqlConnection(ConnectionManager.ConnectionString);
            open();
        }
        private static void solveASPDataDirectory()
        {
            string x = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
            if (AppDomain.CurrentDomain.GetData("DataDirectory") != null && x[x.Length - 1] != 'N')
            {
                string dataDirPath = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
                dataDirPath = dataDirPath.Substring(0, dataDirPath.IndexOf("WEB")) + "BIN";
                AppDomain.CurrentDomain.SetData("DataDirectory", dataDirPath);
            }
        }

        public void open()
        {            
            conn.Open();
        }

        public void close()
        {
            conn.Close();
        }
        public SqlDataReader excuteSP(String spName)
        {
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;

            SqlDataReader rdr = cmd.ExecuteReader();
            return rdr;
        }

        public SqlDataReader excuteSP(String spName, List<SqlParameter> sqlParams)
        {
            SqlCommand cmd = conn.CreateCommand();

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;

            foreach (SqlParameter sqlParam in sqlParams)
            {
                cmd.Parameters.Add(sqlParam);
            }

            SqlParameter param = new SqlParameter("@retVal", SqlDbType.Int);
            param.Direction = ParameterDirection.ReturnValue;
            sqlParams.Add(param);
            cmd.Parameters.Add(param);

            SqlDataReader rdr = cmd.ExecuteReader();

            return rdr;
        }

        public int ExecuteNonQuery(String sql, params Object[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(sql, parameters);

                int rows = command.ExecuteNonQuery();
                command.Connection.Close();

                return rows;
            }
            catch (System.Exception ex)
            {
                return 0;
            }

        }

        private SqlCommand CreateCommand(String sql, params Object[] parameters)
        {
            SqlCommand command = new SqlCommand(sql, conn);
            for (int i = 0; i < parameters.Length; i += 2)
            {
                command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
            }
            return command;
        }

        public object ExecuteScalar(String sql, params Object[] parameters)
        {
            try
            {
                SqlCommand command = CreateCommand(sql, parameters);

                object value = command.ExecuteScalar();
                command.Connection.Close();

                return value;
            }
            catch (System.Exception ex)
            {
                return null;
            }

        }

        public static void Save()
        {
            try
            {
                DB.SubmitChanges();
            }
            catch (System.Exception ex)
            {
                db = new DataClasses_RetailerDataContext(ConnectionManager.ConnectionString);
                throw ex;
            }            
        }
    }
}
